package com.baidu.jingyan.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.baidu.jingyan.model.Jingyan;

public class DBUtil {
	private static DBUtil instance;
	private DBUtil(){}
	public static DBUtil getInstance() {
		if (instance == null) {
			instance = new DBUtil();
		}
		return instance;
	}
	public Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/baidu?useUnicode=true&characterEncoding=UTF-8", "mjteam", "mjteam");
			return connection;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public void saveBysql(String sql) { //values 格式('http://better.apps.cn',1,'appnames'),('http://better.apps.cn',1,'appnames')
		Connection connection = getConnection();
		try {
			connection.setAutoCommit(false);
			Statement stmt = connection.createStatement();
			stmt.execute(sql);
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback(); //不要忘记
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public List<Jingyan> getJingyans() {
		List<Jingyan> jingyans = new ArrayList<Jingyan>();
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("select * from jingyan");
			while (rs.next()) {
				Jingyan jingyan = new Jingyan();
				jingyan.setId(rs.getLong("id"));
				jingyan.setTitle(rs.getString("title"));
				jingyan.setUrl(rs.getString("url"));
				jingyan.setViewCount(rs.getInt("view_count"));
				jingyans.add(jingyan);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return jingyans;
	}
}
